package com.example.springboot.jdbc.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * jdbc原生操作方式
 * 
 * @author Administrator
 * 
 */
public class JdbcUtil {

	private static Log log = LogFactory.getLog(JdbcUtil.class);

	private static String IP = ConfigUtil.getString("config.properties", "sqlServer.Ip", "127.0.0.1");
	private static String PORT = ConfigUtil.getString("config.properties", "sqlServer.Port", "1433");

	private static String DATABASE_NAME = ConfigUtil.getString("config.properties", "sqlServer.databaseName",
			"WFBDCMain");
	private static String USER_NAME = ConfigUtil.getString("config.properties", "sqlServer.user", "nsmc53");
	private static String PASSWORD = ConfigUtil.getString("config.properties", "sqlServer.password", "123456");

	/**
	 * 获取连接
	 * 
	 * @return
	 */
	public static Connection getConnection(String dbName) {

		Connection conn = null;
		try {
			if (dbName == null || "".equals(dbName)) {
				dbName = DATABASE_NAME;
			}
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = String.format("jdbc:sqlserver://%s:%s;databasename=%s;user=%s;password=%s;", IP, PORT, dbName,
					USER_NAME, PASSWORD);
			conn = DriverManager.getConnection(url);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("获取Connection异常： " + e);
		}
		return conn;
	}

	/**
	 * 获取Statement
	 * 
	 * @return
	 */
	public static Statement getStatement(String dbName) {

		Statement statement = null;
		try {
			statement = getConnection(dbName).createStatement();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("获取Statement异常： " + e);
		}
		return statement;
	}

	/**
	 * 关闭连接
	 * 
	 * @param conn
	 */
	public static void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				log.error("关闭连接Connection异常： " + e);
			} finally {
				conn = null;
			}
		}
	}

	/**
	 * 关闭连接
	 * 
	 * @param conn
	 */
	public static void close(Statement state) {
		if (state != null) {
			try {
				state.close();
			} catch (SQLException e) {
				e.printStackTrace();
				log.error("关闭连接Statement异常： " + e);
			} finally {
				state = null;
			}
		}
	}

	/**
	 * 查询kylin数据
	 * 
	 * @param sql
	 * @param t
	 * @return
	 */
	public static Map<String, Object> selectOne(String dbName, String sql) {

		Map<String, Object> map = new HashMap<String, Object>();
		try {
			List<Map<String, Object>> list = select(dbName, sql);
			if (list != null && list.size() > 0) {
				map = list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * 查询kylin数据
	 * 
	 * @param sql
	 * @param t
	 * @return
	 */
	public static List<Map<String, Object>> select(String dbName, String sql) {

		Connection connection = null;
		Statement state = null;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			connection = getConnection(dbName);
			state = connection.createStatement();
			ResultSet rs = state.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int index = 1; index < rsmd.getColumnCount(); index++) {
					String columnName = rsmd.getColumnName(index);
					Object columnValue = rs.getObject(index);
					map.put(columnName, columnValue);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(state);
			close(connection);
		}
		return list;
	}

	/**
	 * 查询kylin数据
	 * 
	 * @param sql
	 * @param t
	 * @return
	 */
	public static <T> T selectOne(String dbName, String sql, Class<?> clazz) {
		Statement state = null;
		T t = null;
		try {
			state = getStatement(dbName);
			ResultSet resultSet = state.executeQuery(sql);
			t = populateOne(resultSet, clazz);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(state);
		}
		return t;
	}

	/**
	 * 查询kylin数据
	 * 
	 * @param sql
	 * @param t
	 * @return
	 */
	public static <T> List<T> select(String dbName, String sql, Class<?> clazz) {
		Statement state = null;
		Connection connection = null;
		List<T> list = null;
		try {
			connection = getConnection(dbName);
			state = connection.createStatement();
			ResultSet resultSet = state.executeQuery(sql);
			list = populate(resultSet, clazz);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(state);
			close(connection);
		}
		return list;
	}

	/**
	 * 将rs结果转换成对象列表
	 * 
	 * @param <T>
	 * 
	 * @param rs
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	public static <T> T populateOne(ResultSet rs, Class<?> clazz) throws Exception {

		List<T> list = populate(rs, clazz);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 将rs结果转换成对象列表
	 * 
	 * @param <T>
	 * 
	 * @param rs
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> populate(ResultSet rs, Class<?> clazz) throws Exception {

		List<T> list = new ArrayList<T>();
		ResultSetMetaData rsmd = rs.getMetaData();
		Field[] fields = clazz.getDeclaredFields();
		while (rs != null && rs.next()) {
			@SuppressWarnings("unchecked")
			T obj = (T) clazz.newInstance();// 构造业务对象实体
			for (int index = 1; index < rsmd.getColumnCount(); index++) {
				String columnName = rsmd.getColumnName(index);
				Object columnValue = rs.getObject(index);

				for (int j = 0; j < fields.length; j++) {
					Field field = fields[j];
					// 如果匹配进行赋值
					if (field.getName().equalsIgnoreCase(columnName)) {
						boolean flag = field.isAccessible();
						field.setAccessible(true);
						field.set(obj, getValue(field, columnValue));
						field.setAccessible(flag);
						break;
					}
				}
			}
			list.add(obj);
		}
		return list;
	}

	/**
	 * 将结果转为对应的字段类型
	 * 
	 * @param field
	 * @param columnValue
	 * @return
	 * @throws ParseException
	 */
	private static Object getValue(Field field, Object value) {

		try {

			if (value == null || "".equals(value) || "null".equals(value)) {
				return null;
			}
			String columnValue = value.toString().trim();
			Class<?> type = field.getType();
			if (type == byte.class || type == Byte.class) {
				return Integer.parseInt(columnValue);
			} else if (type == int.class || type == Integer.class) {
				String str = columnValue;
				if ("false".equals(str)) {
					return 0;
				} else if ("true".equals(str)) {
					return 1;
				} else {
					return Integer.parseInt(str);
				}
			} else if (type == float.class || type == Float.class) {
				return Float.parseFloat(columnValue);
			} else if (type == double.class || type == Double.class) {
				return Double.parseDouble(columnValue);
			} else if (type == Boolean.class) {
				String str = columnValue;
				boolean parseBoolean = Boolean.parseBoolean(str);
				return parseBoolean;
			} else if (type == String.class) {
				return columnValue;
			} else if (type == Date.class) {
				String str = columnValue;
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date date = dateFormat.parse(str);
				return date;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// public static void main(String[] args) {
	// String sql = "select * from ISIP_tb_User";
	// List<User> list = JdbcUtil.select("WFBDCMain", sql, User.class);
	// System.out.println(list);
	// }
}
